package com.day09.homework;

import javax.swing.*;
import java.awt.*;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Homework extends JFrame {

    private static final int WINDOW_WIDTH = 640;
    private static final int WINDOW_HEIGHT = 320;

    private JSplitPane splitPane;
    private JPanel panel1, panel2;
    private JComboBox<Prov> comboBox1, comboBox2;

    private List<String> provStrArr;
    private List<String> cityStrArr;
    private DataManager dataManager;

    public Homework() {
        dataManager = new DataManager();
        initComponent();
        addComponent();
        addListener();
        initWindows();
    }

    private void initComponent() {
        splitPane = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT);
        panel1 = new JPanel();
        panel2 = new JPanel();
        provStrArr = new ArrayList<>();
        cityStrArr = new ArrayList<>();
        provStrArr = dataManager.selectProvAll(DataBaseUtil.openConnection());
        cityStrArr = dataManager.selectCityByProv(DataBaseUtil.openConnection(), provStrArr.get(0));
        comboBox1 = new JComboBox(provStrArr.toArray());
        comboBox2 = new JComboBox(cityStrArr.toArray());
    }

    private void addComponent() {
        panel1.setLayout(new BorderLayout());
        panel2.setLayout(new BorderLayout());
        panel1.add(comboBox1, BorderLayout.CENTER);
        panel2.add(comboBox2, BorderLayout.CENTER);
        splitPane.setLeftComponent(panel1);
        splitPane.setRightComponent(panel2);
        splitPane.setDividerLocation(200);
        splitPane.setDividerSize(2);
        this.add(splitPane);
    }

    private void addListener() {
        comboBox1.addItemListener(
                new ItemListener() {
                    @Override
                    public void itemStateChanged(ItemEvent e) {
                        if (e.getStateChange() == ItemEvent.SELECTED) {
                            String indexStr = (String) comboBox1.getSelectedItem();
                            dataManager = new DataManager();
                            cityStrArr = dataManager.selectCityByProv(DataBaseUtil.openConnection(), indexStr);
                            comboBox2.removeAllItems();
                            comboBox2.setModel(new DefaultComboBoxModel(cityStrArr.toArray()));
                        }
                    }
                }
        );
    }

    private void initWindows() {
        this.setTitle("作业");
        this.setSize(WINDOW_WIDTH, WINDOW_HEIGHT);
        this.setLocationRelativeTo(null);
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setVisible(true);
    }

    public static void main(String[] args) {
        new Homework();
    }

    class DataManager {

        private static final String SQL_1 = "SELECT prov_id, prov_name FROM prov;";
        private static final String SQL_2 = "SELECT city_id,city_name,fk_prov_id FROM city WHERE fk_prov_id=(SELECT prov_id FROM prov WHERE prov_name=?);";

        private PreparedStatement preparedStatement = null;
        private ResultSet resultSet = null;

        public List<String> selectProvAll(Connection connection) {
            List<Prov> list = new ArrayList<>();
            List<String> tmpList = new ArrayList<>();
            preparedStatement = null;
            resultSet = null;
            try {
                preparedStatement = connection.prepareStatement(SQL_1);
                resultSet = preparedStatement.executeQuery();
                Prov prov = new Prov();
                while (resultSet.next()) {
                    prov.setProvId(resultSet.getInt("prov_id"));
                    prov.setProvName(resultSet.getString("prov_name"));
                    tmpList.add(prov.getProvName());
                    list.add(prov);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DataBaseUtil.closeConnection(resultSet, preparedStatement, connection);
            }
            return tmpList;
        }

        public List<String> selectCityByProv(Connection connection, String paraProvName) {
            List<City> list = new ArrayList<>();
            List<String> tmpList = new ArrayList<>();
            try {
                preparedStatement = null;
                resultSet = null;
                preparedStatement = connection.prepareStatement(SQL_2);
                preparedStatement.setString(1, paraProvName);
                resultSet = preparedStatement.executeQuery();
                City city = new City();
                while (resultSet.next()) {
                    city.setCityId(resultSet.getInt("city_id"));
                    city.setCityName(resultSet.getString("city_name"));
                    city.setFkProvId(resultSet.getInt("fk_prov_id"));
                    tmpList.add(city.getCityName());
                    list.add(city);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DataBaseUtil.closeConnection(resultSet, preparedStatement, connection);
            }
            return tmpList;
        }
    }

    static class DataBaseUtil {
        private static final String DB_URL = "jdbc:mysql://localhost:3306/homework1";
        private static final String DB_USERNAME = "root";
        private static final String DB_PASSWORD = "20000628";
        private static final String DB_DRIVER = "com.mysql.cj.jdbc.Driver";

        public static Connection openConnection() {
            Connection connection = null;
            try {
                Class.forName(DB_DRIVER);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            try {
                connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return connection;
        }

        public static void closeConnection(ResultSet resultSet, Statement statement, Connection connection) {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    try {
                        if (connection != null) {
                            connection.close();
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }

    class Prov {
        private Integer provId;
        private String provName;

        public Prov() {
        }

        public Prov(Integer provId, String provName) {
            this.provId = provId;
            this.provName = provName;
        }

        public Integer getProvId() {
            return provId;
        }

        public void setProvId(Integer provId) {
            this.provId = provId;
        }

        public String getProvName() {
            return provName;
        }

        public void setProvName(String provName) {
            this.provName = provName;
        }

        @Override
        public String toString() {
            return "Prov{" +
                    "provId=" + provId +
                    ", provName='" + provName + '\'' +
                    '}';
        }
    }

    class City {
        private Integer cityId;
        private String cityName;
        private Integer fkProvId;

        public City() {
        }

        public City(Integer cityId, String cityName, Integer fkProvId) {
            this.cityId = cityId;
            this.cityName = cityName;
            this.fkProvId = fkProvId;
        }

        public Integer getCityId() {
            return cityId;
        }

        public void setCityId(Integer cityId) {
            this.cityId = cityId;
        }

        public String getCityName() {
            return cityName;
        }

        public void setCityName(String cityName) {
            this.cityName = cityName;
        }

        public Integer getFkProvId() {
            return fkProvId;
        }

        public void setFkProvId(Integer fkProvId) {
            this.fkProvId = fkProvId;
        }

        @Override
        public String toString() {
            return "City{" +
                    "cityId=" + cityId +
                    ", cityName='" + cityName + '\'' +
                    ", fkProvId=" + fkProvId +
                    '}';
        }
    }
}
